DROP PROCEDURE [dbo].[pr0054]
GO
----- Cap nhat lai so du trong truong hop Xoa

CREATE  PROCEDURE [dbo].[pr0054]
	 @DivisionID varchar(20), 
	@WareHouseID  varchar(20),
	 @TranMonth  int, 
	@TranYear int, 
	@InventoryID  varchar(20), 
	@ConvertedAmount as Decimal(28,8), 
	@ConvertedQuantity as Decimal(28,8), 
	@DebitAccountID  varchar(20), 
	@CreditAccountID  varchar(20), 
	@Type as tinyint 

 AS

If @Type =1	 ---- Xoa mot phieu nhap kho
	Begin
		If not exists (Select 1 From tb0106 Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity ,  DebitAmount , CreditQuantity,
					CreditAmount,  EndQuantity ,  EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@DebitAccountID, 0,0, -@ConvertedQuantity, -@ConvertedAmount , 0,0, 
				-@ConvertedQuantity, -@ConvertedAmount)
		else
			Update tb0106
				set 	DebitQuantity 	=	isnull(DebitQuantity,0)	- isnull(@ConvertedQuantity,0),
					DebitAmount 	=	isnull(DebitAmount,0)	- Isnull(@ConvertedAmount,0),
					EndQuantity		=	isnull(EndQuantity,0)		- isnull(@ConvertedQuantity,0),
					EndAmount		=	isnull(EndAmount,0)		- isnull(@ConvertedAmount,0)	
			Where 		InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear	
	

	End
  		---- Type =0, Xoa mot phieu Xuat kho
If @Type =0	
	Begin
	If not exists (Select 1 From tb0106 Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@CreditAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity,  DebitAmount, CreditQuantity,
					CreditAmount,  EndQuantity ,  EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@CreditAccountID, 0,0, 0,0,-@ConvertedQuantity, -@ConvertedAmount , 
				@ConvertedQuantity, @ConvertedAmount)

			
		else
			Update tb0106
				set 	CreditQuantity	 	=	isnull(CreditQuantity,0)		- isnull(@ConvertedQuantity,0),
					CreditAmount 	=		isnull(CreditAmount,0)	- 	isnull(@ConvertedAmount,0),
					EndQuantity		=	isnull(EndQuantity,0)		+ isnull(@ConvertedQuantity,0),
					EndAmount		=	isnull(EndAmount,0)		+ isnull(@ConvertedAmount,0)	
			Where 		InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@CreditAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear	
	

		
	End

-------------------------------------------------
If @Type =11	 ---- Xoa mot phieu nhap kho Van chuyen NB
	Begin
		If not exists (Select 1 From tb0106 Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity ,  DebitAmount , CreditQuantity,
					CreditAmount,  InDebitQuantity, InDebitAmount, EndQuantity ,  EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@DebitAccountID, 0,0, -@ConvertedQuantity, -@ConvertedAmount , 0,0, -@ConvertedQuantity, -@ConvertedAmount,
				-@ConvertedQuantity, -@ConvertedAmount)
		else
			Update tb0106

				set 	DebitQuantity 	=	isnull(DebitQuantity,0)	- isnull(@ConvertedQuantity,0),
					DebitAmount 	=	isnull(DebitAmount,0)	- Isnull(@ConvertedAmount,0),
					InDebitQuantity 	=	isnull(InDebitQuantity,0)	- isnull(@ConvertedQuantity,0),
					InDebitAmount 	=	isnull(InDebitAmount,0)	- Isnull(@ConvertedAmount,0),
					EndQuantity		=	isnull(EndQuantity,0)		- isnull(@ConvertedQuantity,0),
					EndAmount		=	isnull(EndAmount,0)		- isnull(@ConvertedAmount,0)
						
			Where 		InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@DebitAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear	
	

	End

	---- Type =0, Xoa mot phieu Xuat kho
If @Type = 10 
	Begin
	If not exists (Select 1 From tb0106 Where 	InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@CreditAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear)
			Insert tb0106 (InventoryID, WareHouseID,TranMonth,TranYear, DivisionID,
					  InventoryAccountID,   BeginQuantity,  BeginAmount,
					 DebitQuantity,  DebitAmount, CreditQuantity,
					CreditAmount, InCreditQuantity, InCreditAmount,  EndQuantity ,  EndAmount)
			Values	(@InventoryID, @WareHouseID, @TranMonth, @TranYear, @DivisionID,
				@CreditAccountID, 0,0, 0,0,-@ConvertedQuantity, -@ConvertedAmount , 
				-@ConvertedQuantity, -@ConvertedQuantity,  @ConvertedQuantity, @ConvertedAmount)

			
		else
			Update tb0106
				set 	CreditQuantity	 	=		isnull( CreditQuantity,0)		- isnull(@ConvertedQuantity,0),
					CreditAmount 		=		isnull( CreditAmount,0)	- 	isnull(@ConvertedAmount,0),
					InCreditQuantity	 	=		isnull( InCreditQuantity,0)		- isnull(@ConvertedQuantity,0),
					InCreditAmount 		=		isnull( InCreditAmount,0)	- 	isnull(@ConvertedAmount,0),
					EndQuantity		=		isnull( EndQuantity,0)		+ isnull(@ConvertedQuantity,0),
					EndAmount		=		isnull( EndAmount,0)		+ isnull(@ConvertedAmount,0)	
			Where 		InventoryID =@InventoryID and
								DivisionID =@DivisionID and
								WareHouseID =@WareHouseID and
								InventoryAccountID =@CreditAccountID and
								TranMonth =@TranMonth and
								TranYear =@TranYear	
	

		
	End


GO